package handler;

import DataBase.DBMsg;
import DataBase.myDB;
import com.example.banksys.Account;
import com.example.banksys.MainApplication;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.sql.Types;

import static DataBase.DBMsg.State.ERROR;
import static DataBase.DBMsg.State.SUCCESS;

public class tellerbusinessHandler {
    private final myDB database;

    public tellerbusinessHandler(){
        database =new myDB("banksystem");
    }

    public tellerbusinessHandler(String USER){
        database=new myDB("banksystem",USER);
    }

    public DBMsg AccountCreate(String IdentityCode,String acctype,String Premoney,String password) throws SQLException {
        if(!isIDNumber(IdentityCode)){
           return new DBMsg(ERROR,"请输入正确的身份证号！");
        }
        double premoney;//预存金额将默认视为活期
        try{
            premoney=Double.parseDouble(Premoney);
        }catch(NumberFormatException e){
            return new DBMsg(ERROR,"请输入正确的预存金额！");
        }
        String sql="select * from banksystem.identity_customer where identity_ID = "+IdentityCode;
        ResultSet identitySearch=database.getStatement().executeQuery(sql);
        if(!identitySearch.next()){
            PreparedStatement preState=database.getConnection().prepareStatement("insert into banksystem.identity_customer  value (?,?) ");
            preState.setString(1,IdentityCode);
            preState.setNull(2,Types.BIGINT);
            preState.execute();
            //sql="insert into banksystem.identity_customer identity_ID value "+IdentityCode;
            //database.getStatement().execute(sql);
            sql="select * from banksystem.identity_customer where identity_ID = "+IdentityCode;
            identitySearch=database.getStatement().executeQuery(sql);
            identitySearch.next();
        }
        long customerID=identitySearch.getLong("customer_ID");
        Timestamp t=new Timestamp(System.currentTimeMillis());

        //如果这个身份证从未在数据库中出现过，为它记录一个客户ID，这个customer_ID是自动生成的

        PreparedStatement preState=database.getConnection().prepareStatement("insert into banksystem.customer_account  value (?,?,?,?,?,?) ");
        preState.setDouble(1,0);
        preState.setString(2,acctype);
        preState.setTimestamp(3,t);
        preState.setNull(4,Types.BIGINT);
        preState.setString(5,password);
        preState.setLong(6,customerID);
        preState.execute();
        //sql="insert into banksystem.customer_account (customer_ID,balance,type,open_time,password) values ("+customerID+","+premoney+","+acctype+","+t+","+password+")";
        //database.getStatement().execute(sql);
        sql="select max(account_ID) account_ID from banksystem.customer_account where customer_ID= "+customerID;
        identitySearch=database.getStatement().executeQuery(sql);
        identitySearch.next();

        if(premoney!=0){
            myDB sub_DB=new myDB("banksystem");
            String sql1="insert into sub_account values ("+identitySearch.getLong("account_ID")+",2,0,'"+new Timestamp(System.currentTimeMillis())+"','"+new Timestamp(System.currentTimeMillis())+"')";
            sub_DB.getStatement().execute(sql1);
            myDB help=new myDB("banksystem");
            PreparedStatement pret=help.getConnection().prepareStatement("insert into banksystem.trade_record value (?,?,?,?,?,?)");
            pret.setNull(1,Types.BIGINT);
            pret.setDouble(2,premoney);
            pret.setTimestamp(3,new Timestamp(System.currentTimeMillis()));
            pret.setLong(4,MainApplication.tellerID);
            pret.setLong(5,identitySearch.getLong("account_ID"));
            pret.setLong(6,2);
            //String presql="insert into trade_record values (,"+premoney+",,"+MainApplication.tellerID+","+identitySearch.getLong("account_ID")+",save)";
            //help.getStatement().execute(presql);
            pret.execute();
        }
        return new DBMsg(SUCCESS,"开户成功！\n您的账户ID为："+identitySearch.getLong("account_ID"));
    }

   public DBMsg TradeHandle(Account acc,double amount,long typeid,long employeeid,String type) throws SQLException {
        myDB sub_DB=new myDB("banksystem");
        String sql1="select * from sub_account where account_ID = "+acc.getAccount_ID()+" and type_ID=2";
        ResultSet res=sub_DB.getStatement().executeQuery(sql1);
        if(!res.next()){
           sql1="insert into sub_account values ("+acc.getAccount_ID()+",2,0,'"+new Timestamp(System.currentTimeMillis())+"','"+new Timestamp(System.currentTimeMillis())+"')";
           sub_DB.getStatement().execute(sql1);
        }
        if(type.equals("take")||type.equals("financialProduct")){
           amount=(-Math.abs(amount));
        }
        if(res.getDouble("sub_balance")+amount<0){
            return new DBMsg(ERROR,"交易失败：您的账户余额不足！");
        }

        if(!type.equals("take")){
            sql1="select * from sub_account where type_ID="+typeid+" and account_ID="+acc.getAccount_ID();
            ResultSet res1=sub_DB.getStatement().executeQuery(sql1);
            if(!res1.next()){
                sql1="insert into sub_account values ("+acc.getAccount_ID()+","+typeid+",0,'"+new Timestamp(System.currentTimeMillis())+"','"+new Timestamp(System.currentTimeMillis())+"')";
                sub_DB.getStatement().execute(sql1);
            }
        }


        myDB zeroDB=new myDB("banksystem");
        String sql="select teller_ID from employee_teller where employee_ID="+employeeid;
        ResultSet t=zeroDB.getStatement().executeQuery(sql);
        long tellerid = 0;
        if(t.next())
            tellerid=t.getLong(1);
        myDB myDB=new myDB("banksystem");
        PreparedStatement preparedStatement=myDB.getConnection().prepareStatement("insert into trade_record values (?,?,?,?,?,?)");
        preparedStatement.setNull(1,Types.BIGINT);
        preparedStatement.setDouble(2,amount);//这里察觉到type可能是冗余的，对记录查询先进行一个修改
        preparedStatement.setTimestamp(3,new Timestamp(System.currentTimeMillis()));
        preparedStatement.setLong(4, tellerid);
        preparedStatement.setLong(5,acc.getAccount_ID());
        preparedStatement.setLong(6,typeid);
        preparedStatement.execute();
        //这里是交易记录完成,应该是使用触发器，对sub_account中的子账户进行操作。

        return new DBMsg(SUCCESS,"交易成功！");
   }


    public static boolean isIDNumber(String IDNumber) {
        if (IDNumber == null || "".equals(IDNumber)) {
            return false;
        }
        // 定义判别用户身份证号的正则表达式（15位或者18位，最后一位可以为字母）
        String regularExpression = "(^[1-9]\\d{5}(18|19|20)\\d{2}((0[1-9])|(10|11|12))(([0-2][1-9])|10|20|30|31)\\d{3}[0-9Xx]$)|" +
                "(^[1-9]\\d{5}\\d{2}((0[1-9])|(10|11|12))(([0-2][1-9])|10|20|30|31)\\d{3}$)";
        //假设18位身份证号码:41000119910101123X  410001 19910101 123X
        //^开头
        //[1-9] 第一位1-9中的一个      4
        //\\d{5} 五位数字           10001（前六位省市县地区）
        //(18|19|20)                19（现阶段可能取值范围18xx-20xx年）
        //\\d{2}                    91（年份）
        //((0[1-9])|(10|11|12))     01（月份）
        //(([0-2][1-9])|10|20|30|31)01（日期）
        //\\d{3} 三位数字            123（第十七位奇数代表男，偶数代表女）
        //[0-9Xx] 0123456789Xx其中的一个 X（第十八位为校验值）
        //$结尾

        //假设15位身份证号码:410001910101123  410001 910101 123
        //^开头
        //[1-9] 第一位1-9中的一个      4
        //\\d{5} 五位数字           10001（前六位省市县地区）
        //\\d{2}                    91（年份）
        //((0[1-9])|(10|11|12))     01（月份）
        //(([0-2][1-9])|10|20|30|31)01（日期）
        //\\d{3} 三位数字            123（第十五位奇数代表男，偶数代表女），15位身份证不含X
        //$结尾


        boolean matches = IDNumber.matches(regularExpression);

        //判断第18位校验值
        if (matches) {

            if (IDNumber.length() == 18) {
                try {
                    char[] charArray = IDNumber.toCharArray();
                    //前十七位加权因子
                    int[] idCardWi = {7, 9, 10, 5, 8, 4, 2, 1, 6, 3, 7, 9, 10, 5, 8, 4, 2};
                    //这是除以11后，可能产生的11位余数对应的验证码
                    String[] idCardY = {"1", "0", "X", "9", "8", "7", "6", "5", "4", "3", "2"};
                    int sum = 0;
                    for (int i = 0; i < idCardWi.length; i++) {
                        int current = Integer.parseInt(String.valueOf(charArray[i]));
                        int count = current * idCardWi[i];
                        sum += count;
                    }
                    char idCardLast = charArray[17];
                    int idCardMod = sum % 11;
                    if (idCardY[idCardMod].toUpperCase().equals(String.valueOf(idCardLast).toUpperCase())) {
                        return true;
                    } else {
                        System.out.println("身份证最后一位:" + String.valueOf(idCardLast).toUpperCase() +
                                "错误,正确的应该是:" + idCardY[idCardMod].toUpperCase());
                        return false;
                    }

                } catch (Exception e) {
                    e.printStackTrace();
                    System.out.println("异常:" + IDNumber);
                    return false;
                }
            }

        }
        return matches;
    }

}
